__author__ = 'Totomo'

# 修改stu表的数据信息
#
# 导入 pymysql 模块
import pymysql


class stu_sql(object):
    def __init__(self):
        # 获取数据库连接
        self.db = pymysql.connect(host="localhost", user="root", password="4444", db="test", charset="utf8")

    """
    def change_sql(self,data):
        # 创建游标对象
        cursor = self.db.cursor()
        # 定义sql语句
        sql = "update stu set name='%s',age='%d',sex='%s',classid='%s' where id=%d" % data

        try:
            # 执行sql操作
            m = cursor.execute(sql)
            # 事务提交
            self.db.commit()

            print("成功修改条数：", m)

        except Exception as err:
            # 事务回滚
            self.db.rollback()
            print("SQL执行错误！原因：", err)
    """

    def findAll(self):
        cursor = self.db.cursor()
        # 定义sql语句
        # sql = "select * from stu"
        sql = "select * from stu"

        try:
            # 执行sql操作
            cursor.execute(sql)

            print("结果条数：", cursor.rowcount)
            # 解析结果(遍历结果)
            '''
            # 使用fetchone（）
            while True:
                data = cursor.fetchone()
                if data == None:
                    break;
                print(data)
            '''
            ll = cursor.fetchall()
            return ll
        except Exception as err:
            print("SQL执行错误！原因：", err)

    def insert(self, data):
        # 创建游标对象
        cursor = self.db.cursor()
        # 定义sql语句
        data = tuple(data)
        sql = "insert into test.stu(name,age,classid) values('%s',%d,'%s')" % data

        try:
            # 执行sql操作
            m = cursor.execute(sql)
            # 事务提交
            self.db.commit()

            print("成功修改条数：", m)

        except Exception as err:
            # 事务回滚
            self.db.rollback()
            print("SQL执行错误！原因：", err)

    def delete(self,id):
        # 创建游标对象
        cursor = self.db.cursor()
        # 定义sql语句
        sql = "delete from test.stu where id = '%d'" % id

        try:
            # 执行sql操作
            m = cursor.execute(sql)
            # 事务提交
            self.db.commit()

            print("成功修改条数：", m)

        except Exception as err:
            # 事务回滚
            self.db.rollback()
            print("SQL执行错误！原因：", err)

    def __del__(self):
        # 关闭数据库连接
        self.db.close()

